package basic11;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

//data access object
public class J20_bookDBDAO implements J20_bookDBService {
	// JDBC 접속 정보
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";

	// SQL문 작성
	private static final String insertSQL = "insert into bookdb (id,title,publisher,author) values(seq_id.nextval,?,?,?)";
	private static final String selectSQL = "select id,title from bookdb order by id asc";
	private static final String selectSQL2 = "select * from bookdb where id=? order by id asc";
	private static final String updateSQL = "update bookdb set title=?, publisher=?, author=? where id=?";
	private static final String deleteSQL = "delete from bookdb where id=?";

	@Override
	public int insertBook(J20_bookDBVO dvo) {
		Connection conn = null; // JDBC에 접속하기 위해 작성
		PreparedStatement ps = null; // SQL문을 셋팅하기 위해서 작성
		int result = 0;
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);

			// 매개변수에 들어갈 실제값을 지정
			ps.setString(1, dvo.getTitle());
			ps.setString(2, dvo.getPublilsher());
			ps.setString(3, dvo.getAutor());

			int cnt = ps.executeUpdate();
			if (cnt > 0)
				result = 1;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch (Exception e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
				}
		}
		return result;
	}

	@Override
	public String selectBook() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; // select 를 실행한 결과를 저장해놓는 객체
		String result = "";

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();

			while (rs.next()) {
				for (int i = 1; i <= 2; i++) {
					result += rs.getString(i) + "";
					
				}
				result += "\n";
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (Exception e) {
				}
			if (ps != null)
				try {
					ps.close();
				} catch (Exception e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
				}
		}

		return result;
	}

	@Override
	public String selectOneBook(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";

		try {
			Class.forName(DRV); // 드라이버 탑재 시키는 거
			conn = DriverManager.getConnection(URL, USR, PWD); // 접속하기위해서 아이디랑
																// 불러오는거
			ps = conn.prepareStatement(selectSQL2); // Query문 실행
			ps.setString(1, id);
			rs = ps.executeQuery(); // rs에 값을 저장
			while (rs.next()) {
				for (int i = 1; i <= 5; i++) {
					result += rs.getString(i);
				}
				result += "\n";
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (Exception e) {
				}
			if (ps != null)
				try {
					ps.close();
				} catch (Exception e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
				}
		}
		return result;
	}

	@Override
	public int updateBook(J20_bookDBVO dvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int result = 0;

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);
			ps.setString(1, dvo.getTitle());
			ps.setString(2, dvo.getPublilsher());
			ps.setString(3, dvo.getAutor());
			ps.setString(4, dvo.getId());
			int cnt = ps.executeUpdate();
			
			if (cnt > 0)
				result = 1;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (ps != null)
				try {
				} catch (Exception e) {
				}
			if (conn != null)
				try {
				} catch (Exception e) {
				}
		}
		return result;
	}

	@Override
	public int deleteBook(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		int result = 0;

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSQL);
			ps.setString(1, id);
			int cnt = ps.executeUpdate();
			if (cnt > 0)
				result=1;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch (Exception e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
				}
		}
		return result;
	}

}
